package core;

import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;

import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.ListEntry;
import com.google.gdata.data.spreadsheet.ListFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.util.ServiceException;

public class SpreadsheetParser {

	/**
	 * @param args
	 * @throws IOException
	 * @throws ServiceException
	 */
	public static void main(String[] args) throws IOException, ServiceException {
		// load properties
		Properties configFile = new Properties();
		configFile.load(ListGenerator.class.getClassLoader()
				.getResourceAsStream("config.properties"));

		// get service
		SpreadsheetService client = new SpreadsheetService(
				configFile.getProperty("appName"));
		client.setUserCredentials(configFile.getProperty("login"),
				configFile.getProperty("password"));

		// get feed
		URL metafeedUrl = new URL(
				"https://spreadsheets.google.com/feeds/spreadsheets/private/full");
		SpreadsheetFeed feed = client.getFeed(metafeedUrl,
				SpreadsheetFeed.class);

		// list spreadsheets
		List<SpreadsheetEntry> spreadsheets = feed.getEntries();

		SpreadsheetEntry spreadsheetEntry = null;
		for (SpreadsheetEntry entry : spreadsheets) {
			if (entry.getTitle().getPlainText().equalsIgnoreCase("openings")) {
				spreadsheetEntry = entry;
				break;
			}
		}
		System.out.println("\t" + spreadsheetEntry.getTitle().getPlainText());
		System.out.println(spreadsheetEntry.getKey());

		// list worksheets
		List<WorksheetEntry> worksheets = spreadsheetEntry.getWorksheets();

		WorksheetEntry worksheetEntry = worksheets.get(0);

		String title = worksheetEntry.getTitle().getPlainText();
		int rowCount = worksheetEntry.getRowCount();
		int colCount = worksheetEntry.getColCount();
		System.out.println("\t" + title + "- rows:" + rowCount + " cols: "
				+ colCount);

		// Delete existing rows
		URL listFeedUrl = worksheetEntry.getListFeedUrl();
		ListFeed listFeed = client.getFeed(listFeedUrl, ListFeed.class);
		for (ListEntry entry : listFeed.getEntries()) {
			entry.delete();
			System.out.println(entry.getTitle().getPlainText());
			// for (String tag : entry.getCustomElements().getTags()) {
			// System.out.println("  <gsx:" + tag + ">"
			// + entry.getCustomElements().getValue(tag) + "</gsx:"
			// + tag + ">");
			// }
		}

		// Load new rows from file
		File file = new File("data/jobs.txt");
		Scanner scanner = new Scanner(file, "UTF-8");
		scanner.useDelimiter("[\t\n]");
		while (scanner.hasNext()) {
			String date = scanner.next();
			String location = scanner.next();
			String description = scanner.next();
			scanner.next();
			String abs_link = scanner.next();
			// add row
			ListEntry newEntry = new ListEntry();

			newEntry.getCustomElements().setValueLocal("DateSeen", date);
			newEntry.getCustomElements().setValueLocal("Location", location);
			newEntry.getCustomElements().setValueLocal("Title", description);
			newEntry.getCustomElements().setValueLocal("Details", abs_link);
			ListEntry insertedRow = client.insert(listFeedUrl, newEntry);
			// System.out.println(insertedRow.getId());
		}
		scanner.close();

		// Fix links
		URL cellFeedUrl = worksheetEntry.getCellFeedUrl();
		CellFeed cellFeed = client.getFeed(cellFeedUrl, CellFeed.class);
		for (CellEntry cell : cellFeed.getEntries()) {
			if (cell.getPlainTextContent().startsWith("http:")) {
				System.out.println(cell.getTitle().getPlainText() + " - "
						+ cell.getPlainTextContent());
				String link = cell.getCell().getValue();
				link = link.trim();
				link = "=hyperlink(\"" + link + "\";\"Details\")";
				cell.changeInputValueLocal(link);
				cell.update();
			}
			// String shortId = cell.getId().substring(
			// cell.getId().lastIndexOf('/') + 1);
			// System.out.println(" -- Cell(" + shortId + "/"
			// + cell.getTitle().getPlainText() + ") formula("
			// + cell.getCell().getInputValue() + ") numeric("
			// + cell.getCell().getNumericValue() + ") value("
			// + cell.getCell().getValue() + ")");
		}

	}

}
